﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IBLL;
using System.Data;
using System.Data.Common;

namespace MisBLL
{
    public class GoodsDAO : BaseDAO, IGoods
    {

        //查询商品档案列表
        public DataTable GetAll()
        {
            string sql = "select * from GOODS_TAB";
            return dbHelper.ExecuteDataTable(sql);
        }

        //添加商品档案信息
        public int Add(MisModel.Goods obj)
        {
            if (string.IsNullOrEmpty(obj.F1.ToString()))
            {
                obj.F1 = "1";
            }
            string insertSql = "insert into GOODS_TAB(ID,DM,TM,M_ID,GOODS,DEP_ID,SL0,SL1,JE1,SL2,JE2,SL3,JE3,STORE,PACK,UNIT,UNIT_P,Tax,State,Producter,Bei,Date,Oper,Type1,Type2,Type3,Slyc,Jeyc,Bac_No,SUPP_ID,PRICE1,Price_S,news,SL_last,Je_last,date_1,date_last,date_0,Day_jh,Sl_jh,Sl_max,Sl_min,Fk_id,Jh_id,DAY_JS,TYPE_JS,BATS,BAT,SL_b,Day11,Day12,F0,F1,F2,F3,F4,F5,F81,F82,F83,F84,DATE1,DATE2,ycsl,ycje) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}','{42}','{43}','{44}','{45}','{46}','{47}','{48}','{49}','{50}','{51}','{52}','{53}','{54}','{55}','{56}','{57}','{58}','{59}','{60}','{61}','{62}','{63}','{64}')";
            object[] args = new object[65];
            args[0] = obj.Id;
            args[1] = obj.Dm;
            args[2] = obj.Tm;
            args[3] = obj.MId;
            args[4] = obj.Goods1;
            args[5] = obj.DepId;
            args[6] = obj.Sl0;
            args[7] = obj.Sl1;
            args[8] = obj.Je1;
            args[9] = obj.Sl2;
            args[10] = obj.Je2;
            args[11] = obj.Sl3;
            args[12] = obj.Je3;
            args[13] = obj.Store;
            args[14] = obj.Pack;
            args[15] = obj.Unit;
            args[16] = obj.UnitP;
            args[17] = obj.Tax;
            args[18] = obj.State;
            args[19] = obj.Producter;
            args[20] = obj.Bei;
            args[21] = obj.Date;
            args[22] = obj.Oper;
            args[23] = obj.Type1;
            args[24] = obj.Type2;
            args[25] = obj.Type3;
            args[26] = obj.Slyc;
            args[27] = obj.Jeyc;
            args[28] = obj.BacNo;
            args[29] = obj.SuppId;
            args[30] = obj.Price1;
            args[31] = obj.PriceS;
            args[32] = obj.News;
            args[33] = obj.SlLast;
            args[34] = obj.JeLast;
            args[35] = obj.Date_1;
            args[36] = obj.Date_Last;
            args[37] = obj.Date_0;
            args[38] = obj.DayJh;
            args[39] = obj.SlJh;
            args[40] = obj.SlMax;
            args[41] = obj.SlMin;
            args[42] = obj.FkId;
            args[43] = obj.JhId;
            args[44] = obj.DayJs;
            args[45] = obj.TypeJs;
            args[46] = obj.Bats;
            args[47] = obj.Bat;
            args[48] = obj.SlB;
            args[49] = obj.Day11;
            args[50] = obj.Day12;
            args[51] = obj.F0;
            args[52] = obj.F1;
            args[53] = obj.F2;
            args[54] = obj.F3;
            args[55] = obj.F4;
            args[56] = obj.F5;
            args[57] = obj.F81;
            args[58] = obj.F82;
            args[59] = obj.F83;
            args[60] = obj.F84;
            args[61] = obj.Date1;
            args[62] = obj.Date2;
            args[63] = obj.Ycsl;
            args[64] = obj.Ycje;
            string sql = string.Format(insertSql, args);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //修改商品档案信息
        public int Update(MisModel.Goods obj)
        {
            string updateSql = "update GOODS_TAB set DM ='{0}',TM,= '{1}',M_ID= '{2}',GOODS= '{3}',DEP_ID= '{4}',SL0= '{5}',SL1= '{6}',JE1= '{7}',SL2= '{8}',JE2= '{9}',SL3= '{10}',JE3= '{11}',STORE= '{12}',PACK= '{13}',UNIT= '{14}',UNIT_P= '{15}',Tax= '{16}',State= '{17}',Producter= '{18}',Bei= '{19}',Date= '{20}',Oper= '{21}',Type1= '{22}',Type2= '{23}',Type3= '{24}',Slyc= '{25}',Jeyc= '{26}',Bac_No= '{27}',SUPP_ID= '{28}',PRICE1= '{29}',Price_S= '{30}',news= '{31}',SL_last= '{32}',Je_last= '{33}',date_1= '{34}',date_last= '{35}',date_0= '{36}',Day_jh= '{37}',Sl_jh= '{38}',Sl_max= '{39}',Sl_min= '{40}',Fk_id= '{41}',Jh_id= '{42}',DAY_JS= '{43}',TYPE_JS= '{44}',BATS= '{45}',BAT= '{46}',SL_b= '{47}',Day11= '{48}',Day12= '{49}',F0= '{50}',F1= '{51}',F2= '{52}',F3= '{53}',F4= '{54}',F5= '{55}',F81= '{56}',F82= '{57}',F83= '{58}',F84= '{59}',DATE1= '{60}',DATE2= '{61}',ycsl= '{62}',ycje= '{63}' where ID = '{64}'";
            object[] args = new object[65];
            args[0] = obj.Dm;
            args[1] = obj.Tm;
            args[2] = obj.MId;
            args[3] = obj.Goods1;
            args[4] = obj.DepId;
            args[5] = obj.Sl0;
            args[6] = obj.Sl1;
            args[7] = obj.Je1;
            args[8] = obj.Sl2;
            args[9] = obj.Je2;
            args[10] = obj.Sl3;
            args[11] = obj.Je3;
            args[12] = obj.Store;
            args[13] = obj.Pack;
            args[14] = obj.Unit;
            args[15] = obj.UnitP;
            args[16] = obj.Tax;
            args[17] = obj.State;
            args[18] = obj.Producter;
            args[19] = obj.Bei;
            args[20] = obj.Date;
            args[21] = obj.Oper;
            args[22] = obj.Type1;
            args[23] = obj.Type2;
            args[24] = obj.Type3;
            args[25] = obj.Slyc;
            args[26] = obj.Jeyc;
            args[27] = obj.BacNo;
            args[28] = obj.SuppId;
            args[29] = obj.Price1;
            args[30] = obj.PriceS;
            args[31] = obj.News;
            args[32] = obj.SlLast;
            args[33] = obj.JeLast;
            args[34] = obj.Date_1;
            args[35] = obj.Date_Last;
            args[36] = obj.Date_0;
            args[37] = obj.DayJh;
            args[38] = obj.SlJh;
            args[39] = obj.SlMax;
            args[40] = obj.SlMin;
            args[41] = obj.FkId;
            args[42] = obj.JhId;
            args[43] = obj.DayJs;
            args[44] = obj.TypeJs;
            args[45] = obj.Bats;
            args[46] = obj.Bat;
            args[47] = obj.SlB;
            args[48] = obj.Day11;
            args[49] = obj.Day12;
            args[50] = obj.F0;
            args[51] = obj.F1;
            args[52] = obj.F2;
            args[53] = obj.F3;
            args[54] = obj.F4;
            args[55] = obj.F5;
            args[56] = obj.F81;
            args[57] = obj.F82;
            args[58] = obj.F83;
            args[59] = obj.F84;
            args[60] = obj.Date1;
            args[61] = obj.Date2;
            args[62] = obj.Ycsl;
            args[63] = obj.Ycje;
            args[64] = obj.Id;
            string sql = string.Format(updateSql, args);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //根据ID删除商品档案信息
        public int Delete1(string id)
        {
            string sql = string.Format("delete from GOODS_TAB where ID='{0}'", id);
            return dbHelper.ExecuteNonQuery(sql);
        }

        //根据DataTable得到实体对象
        public MisModel.Goods GetEntity(DataTable dt)
        {
            MisModel.Goods entity = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                entity = new MisModel.Goods();
                entity.Id = dt.Rows[0]["ID"].ToString();
                entity.Dm = dt.Rows[0]["DM"].ToString();
                entity.Tm = dt.Rows[0]["TM"].ToString();
                entity.MId = dt.Rows[0]["M_ID"].ToString();
                entity.Goods1 = dt.Rows[0]["GOODS"].ToString();
                entity.DepId = dt.Rows[0]["DEP_ID"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL0"].ToString()))
                    entity.Sl0 = decimal.Parse(dt.Rows[0]["SL0"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL1"].ToString()))
                    entity.Sl1 = decimal.Parse(dt.Rows[0]["SL1"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["JE1"].ToString()))
                    entity.Je1 = decimal.Parse(dt.Rows[0]["JE1"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL2"].ToString()))
                    entity.Sl2 = decimal.Parse(dt.Rows[0]["SL2"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["JE2"].ToString()))
                    entity.Je2 = decimal.Parse(dt.Rows[0]["JE2"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL3"].ToString()))
                    entity.Sl3 = decimal.Parse(dt.Rows[0]["SL3"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["JE3"].ToString()))
                    entity.Je3 = decimal.Parse(dt.Rows[0]["JE3"].ToString());
                entity.Store = dt.Rows[0]["STORE"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["PACK"].ToString()))
                    entity.Pack = int.Parse(dt.Rows[0]["PACK"].ToString());
                entity.Unit = dt.Rows[0]["UNIT"].ToString();
                entity.UnitP = dt.Rows[0]["UNIT_P"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["Tax"].ToString()))
                    entity.Tax = int.Parse(dt.Rows[0]["Tax"].ToString());
                entity.State = dt.Rows[0]["State"].ToString();
                entity.Producter = dt.Rows[0]["Producter"].ToString();
                entity.Bei = dt.Rows[0]["Bei"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["Date"].ToString()))
                    entity.Date = DateTime.Parse(dt.Rows[0]["Date"].ToString());
                entity.Oper = dt.Rows[0]["Oper"].ToString();
                entity.Type1 = dt.Rows[0]["Type1"].ToString();
                entity.Type2 = dt.Rows[0]["Type2"].ToString();
                entity.Type3 = dt.Rows[0]["Type3"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["Slyc"].ToString()))
                    entity.Slyc = decimal.Parse(dt.Rows[0]["Slyc"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Jeyc"].ToString()))
                    entity.Jeyc = decimal.Parse(dt.Rows[0]["Jeyc"].ToString());
                entity.BacNo = dt.Rows[0]["Bac_No"].ToString();
                entity.SuppId = dt.Rows[0]["SUPP_ID"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["PRICE1"].ToString()))
                    entity.Price1 = decimal.Parse(dt.Rows[0]["PRICE1"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Price_S"].ToString()))
                    entity.PriceS = decimal.Parse(dt.Rows[0]["Price_S"].ToString());
                entity.News = dt.Rows[0]["news"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL_last"].ToString()))
                    entity.SlLast = decimal.Parse(dt.Rows[0]["SL_last"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Je_last"].ToString()))
                    entity.JeLast = decimal.Parse(dt.Rows[0]["Je_last"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["date_1"].ToString()))
                    entity.Date_1 = DateTime.Parse(dt.Rows[0]["date_1"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["date_last"].ToString()))
                    entity.Date_Last = DateTime.Parse(dt.Rows[0]["date_last"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["date_0"].ToString()))
                    entity.Date_0 = DateTime.Parse(dt.Rows[0]["date_0"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Day_jh"].ToString()))
                    entity.DayJh = int.Parse(dt.Rows[0]["Day_jh"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Sl_jh"].ToString()))
                    entity.SlJh = int.Parse(dt.Rows[0]["Sl_jh"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Sl_max"].ToString()))
                    entity.SlMax = int.Parse(dt.Rows[0]["Sl_max"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Sl_min"].ToString()))
                    entity.SlMin = int.Parse(dt.Rows[0]["Sl_min"].ToString());
                entity.FkId = dt.Rows[0]["Fk_id"].ToString();
                entity.JhId = dt.Rows[0]["Jh_id"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["DAY_JS"].ToString()))
                    entity.DayJs = int.Parse(dt.Rows[0]["DAY_JS"].ToString());
                entity.TypeJs = dt.Rows[0]["TYPE_JS"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["BATS"].ToString()))
                    entity.Bats = int.Parse(dt.Rows[0]["BATS"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["BAT"].ToString()))
                    entity.Bat = int.Parse(dt.Rows[0]["BAT"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["SL_b"].ToString()))
                    entity.SlB = decimal.Parse(dt.Rows[0]["SL_b"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Day11"].ToString()))
                    entity.Day11 = int.Parse(dt.Rows[0]["Day11"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["Day12"].ToString()))
                    entity.Day12 = int.Parse(dt.Rows[0]["Day12"].ToString());
                entity.F0 = dt.Rows[0]["F0"].ToString();
                entity.F1 = dt.Rows[0]["F1"].ToString();
                entity.F2 = dt.Rows[0]["F2"].ToString();
                entity.F3 = dt.Rows[0]["F3"].ToString();
                entity.F4 = dt.Rows[0]["F4"].ToString();
                entity.F5 = dt.Rows[0]["F5"].ToString();
                entity.F81 = dt.Rows[0]["F81"].ToString();
                entity.F82 = dt.Rows[0]["F82"].ToString();
                entity.F83 = dt.Rows[0]["F83"].ToString();
                entity.F84 = dt.Rows[0]["F84"].ToString();
                if (!string.IsNullOrEmpty(dt.Rows[0]["DATE1"].ToString()))
                    entity.Date1 = DateTime.Parse(dt.Rows[0]["DATE1"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["DATE2"].ToString()))
                    entity.Date2 = DateTime.Parse(dt.Rows[0]["DATE2"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["ycsl"].ToString()))
                    entity.Ycsl = decimal.Parse(dt.Rows[0]["ycsl"].ToString());
                if (!string.IsNullOrEmpty(dt.Rows[0]["ycje"].ToString()))
                    entity.Ycje = decimal.Parse(dt.Rows[0]["ycje"].ToString());

            }
            return entity;
        }

        public void Create(MisEntity.Goods entity)
        {
            dbContext.Goods.InsertOnSubmit(entity);
            dbContext.SubmitChanges();
        }



        public MisEntity.Goods FindByDM(string DM)
        {
            var query = from item in dbContext.Goods
                        where item.DM == DM
                        select item;
            if (query.Count<MisEntity.Goods>() == 0)
                return null;
            return query.First<MisEntity.Goods>();
        }

        public MisEntity.Goods Load(string Id)
        {
            var query = from item in dbContext.Goods
                        where item.ID == Id
                        select item;
            if (query.Count<MisEntity.Goods>() == 0)
                return null;
            return query.First<MisEntity.Goods>();
        }

        public MisEntity.Goods EndsWithID(string ID)
        {
            var query = from item in dbContext.Goods
                        where item.ID.EndsWith(ID)
                        select item;
            if (query.Count<MisEntity.Goods>() == 0)
                return null;
            return query.First<MisEntity.Goods>();
        }

        public MisEntity.Goods FindByCode(string Code)
        {
            var query = from item in dbContext.Goods
                        where item.DM == Code || item.ID == Code
                        select item;
            if (query.Count<MisEntity.Goods>() == 0)
                return null;
            return query.First<MisEntity.Goods>();
        }

        public void Update(MisEntity.Goods entity)
        {
            MisEntity.Goods info = dbContext.Goods.First<MisEntity.Goods>(item => item.ID == entity.ID);
            info.DM = entity.DM;
            info.STORE = entity.STORE;
            info.UNIT = entity.UNIT;
            info.UNIT_P = entity.UNIT_P;
            info.Type1 = entity.Type1;
            info.Type2 = entity.Type2;
            info.Type3 = entity.Type3;
            info.State = entity.State;
            info.Producter = entity.Producter;
            info.Bei = entity.Bei;
            info.Day11 = entity.Day11;
            info.Jh_id = entity.Jh_id;
            info.Day_jh = entity.Day_jh;
            info.Fk_id = entity.Fk_id;
            info.DAY_JS = entity.DAY_JS;
            info.Sl_max = entity.Sl_max;
            info.Sl_min = entity.Sl_min;
            info.F1 = entity.F1;
            info.F2 = entity.F2;
            info.F3 = entity.F3;
            dbContext.SubmitChanges();
        }

        public void Delete(string Id)
        {
            MisEntity.Goods info = dbContext.Goods.First<MisEntity.Goods>(item => item.ID == Id);
            dbContext.Goods.DeleteOnSubmit(info);
            dbContext.SubmitChanges();
        }

        public void Delete(MisEntity.Goods entity)
        {
            var userList = from info in dbContext.Goods
                           where info.ID == entity.ID
                           select info;
            foreach (var user in userList)
            {
                dbContext.Goods.DeleteOnSubmit(user);
            }
            dbContext.SubmitChanges();
        }

        public IList<MisEntity.Goods> GetList()
        {
            IList<MisEntity.Goods> list = dbContext.Goods.ToList<MisEntity.Goods>();
            return list;
        }

        //获取无销售商品库存查询数据
        public DataTable GetGoodsEnquiriesAll(string sql, string sql1, string tableName, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select M.ID,M.GOODS,M.SL1,M.UNIT,M.JE1,M.PRICE1,M.DATE_LAST,M.DEP_ID,M.BAT,M.BATS,M.type_js,M.BAC_NO,M.SUPP_ID,D.SUPPLY,M.tax,");
            sb.Append("M.F1,M.F2,M.F3,M.DATE_0,M.dm,M.type1,m.SLYC,m.JEYC,M.sl0,m.sl2,m.sl3 from ").Append(tableName);
            sb.Append(" M,SUPPLY_TAB D where").Append(sql).Append(" and m.id not in ( select a.id from sacf_tab a where");
            sb.Append(sql1).Append(" group by a.id) order by M.GOODS");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取表数据
        public DataTable getTableData(string tableName)
        {
            string tableSql = "exec SP_TABLES {0}";
            object[] args = new object[]{tableName};
            string sql = string.Format(tableSql, args);
            DbCommand cmd = dbHelper.GetSqlStringCommond(sql);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取库存查询数据
        public DataTable GetStockEnquiriesAll(string sql, string tableName, Dictionary<string, object> dictionary)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select M.ID,M.GOODS,M.SL1,M.pack,M.JE1,M.PRICE1,M.DATE_LAST,M.DEP_ID,M.BAT,M.BATS,M.type_js,M.BAC_NO,M.SUPP_ID,D.SUPPLY,m.tax,");
            sb.Append("M.F1,M.F3,M.F2,M.DATE_0,M.dm,m.producter,m.SLYC,m.JEYC,m.UNIT,M.unit_p,m.sl0,m.sl2,m.sl3 from ").Append(tableName);
            sb.Append(" M,SUPPLY_TAB D where").Append(sql).Append(" order by M.GOODS");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //获取库存统计数据
        public DataTable GetStockStatisticsAll(string sql, Dictionary<string, object> dictionary, string tjType, String tableName)
        {
            StringBuilder sb = new StringBuilder();
            if ("按供应商".Equals(tjType))
            {
                sb.Append("select M.supp_id,M.DEP_ID,D.DEPART,sum(M.SL1),sum(M.SL0),sum(M.SL2),sum(M.SL3),sum(M.PRICE1*m.sl1),sum(M.JE1),s.allname from ");
                sb.Append(tableName).Append(" M,DEPART_TAB D,supply_tab s where").Append(sql);
                sb.Append("  AND M.DEP_ID=D.DEP_ID and m.supp_id=s.supp_id GROUP BY M.supp_id,M.DEP_ID,D.DEPART,s.allname ORDER BY M.supp_id,M.DEP_ID");
            }
            else if ("按部门".Equals(tjType))
            {
                sb.Append("select substring(M.DEP_ID,1,2),D.DEPART,sum(M.SL1),sum(M.SL0),sum(M.SL2),sum(M.SL3),sum(M.PRICE1*m.sl1),sum(M.JE1) from ");
                sb.Append(tableName).Append(" M,DEPART_TAB D  where").Append(sql);
                sb.Append(" and substring(m.dep_id,1,2)=d.dep_id GROUP BY substring(M.DEP_ID,1,2),D.DEPART ORDER BY substring(M.DEP_ID,1,2)");
            }
            else if ("按商场".Equals(tjType))
            {
                sb.Append("select substring(M.DEP_ID,1,1),D.DEPART,sum(M.SL1),sum(M.SL0),sum(M.SL2),sum(M.SL3),sum(M.PRICE1*m.sl1),sum(M.JE1) from ");
                sb.Append(tableName).Append(" M,DEPART_TAB D  where").Append(sql);
                sb.Append(" and substring(m.dep_id,1,1)=d.dep_id GROUP BY substring(M.DEP_ID,1,1),D.DEPART ORDER BY substring(M.DEP_ID,1,1)");
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //商品价格设置查询
        public DataTable GetGoodsAll(string sql, Dictionary<string, object> dictionary, string tjType)
        {
            StringBuilder sb = new StringBuilder();
            if ("未设置".Equals(tjType))
            {
                sb.Append("select m.id,0,m.price_s,m.je1,m.type_js,m.goods,m.unit,m.pack,m.state,m.tax,m.sl1,m.sl0,m.type1,m.type2,m.type3,'','',m.dep_id,m.date from goods_tab m where 1=1");
                sb.Append(sql).Append(" and m.id not in (select id from goodscus_tab where cus_id=@cusId and cus_dep=@cusDep)");
            }
            else if ("已设置".Equals(tjType))
            {
                sb.Append("select g.id,m.price_s,g.price_l,m.je1,g.type_js,m.goods,m.unit,m.pack,m.state,m.tax,m.sl1,m.sl0,m.type1,m.type2,m.type3,g.CUS_ID");
                sb.Append(",g.cus_dep,m.dep_id,m.date from goodscus_tab g,goods_tab m where m.id=g.id").Append(sql); 
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //商品价格设置查询2
        public DataTable GetGoodsAll2(string sql, Dictionary<string, object> dictionary, string tjType)
        {
            StringBuilder sb = new StringBuilder();
            if ("未设置".Equals(tjType))
            {
                sb.Append("select m.id,0,m.price_s,m.je1,m.type_js,m.goods,m.unit,m.pack,m.state,m.tax,m.sl1,m.sl0,m.type1,m.type2,m.type3,'','',m.dep_id,m.date from goods_tab m where 1=1");
                sb.Append(sql).Append(" and m.id not in (select id from goodscus_tab where 1=1)");
            }
            else if ("已设置".Equals(tjType))
            {
                sb.Append("select g.id,m.price_s,g.price_l,m.je1,g.type_js,m.goods,m.unit,m.pack,m.state,m.tax,m.sl1,m.sl0,m.type1,m.type2,m.type3,g.CUS_ID");
                sb.Append(",g.cus_dep,m.dep_id,m.date from goodscus_tab g,goods_tab m where m.id=g.id").Append(sql);
            }
            DbCommand cmd = dbHelper.GetSqlStringCommond(sb.ToString());
            dbHelper.AddParameter(cmd, dictionary);
            return dbHelper.ExecuteDataTable(cmd);
        }

        //商品售价设置
        public void saveGoodsPrice(IList<MisEntity.Goods> list,string cusId, string cusDep)
        {
            string confirmSql = "exec SP_INSERT_GOODSCUS '{0}','{1}','{2}','{3}','{4}','{5}','{6}'";
            object[] args = new object[7];
            foreach (MisEntity.Goods L_Goods in list)
            {
                DateTime dt = DateTime.Now;
                args[0] = L_Goods.ID;
                args[1] = cusId;
                args[2] = cusDep;
                args[3] = L_Goods.TYPE_JS;
                args[4] = L_Goods.DAY_JS;
                args[5] = L_Goods.Price_S;
                args[6] = L_Goods.Oper;
                string sql = string.Format(confirmSql, args);
                dbHelper.ExecuteNonQuery(sql);
            }
        }

    }
}

